package zry.Achievement.Textbook;

import zry.Search.SearchCriteriaTextbook;
import zry.Util.DBUtil;
import Connection.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TextbookDAOImpl extends DAOBase implements TextbookDAO {
    private static final String TEXTBOOK_INSERT_SQL = "INSERT INTO textbook_table(achievement_id,textbook_name,textbook_publisher,textbook_time,contribution,supporting) VALUES(?,?,?,?,?,?)";
    private static final String TEXTBOOK_UPDATE_SQL = "UPDATE textbook_table SET textbook_name=?,textbook_publisher=?,textbook_time=?,contribution=?,supporting=? WHERE achievement_id=?";
    private static final String TEXTBOOK_SELECT_SQL = "SELECT * FROM textbook_table WHERE achievement_id=?";
    private static final String TEXTBOOK_SELECT_ALL_SQL = "SELECT * FROM textbook_table";

    @Override
    public boolean addTextbook(Textbook textbook) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(TEXTBOOK_INSERT_SQL);
            psmt.setString(1, DBUtil.fixSqlFieldValue(textbook.getA_id()));
            psmt.setString(2, DBUtil.fixSqlFieldValue(textbook.getT_name()));
            psmt.setString(3, DBUtil.fixSqlFieldValue(textbook.getT_publisher()));
            psmt.setDate(4, textbook.getT_time());
            psmt.setString(5, DBUtil.fixSqlFieldValue(textbook.getT_contribution()));
            psmt.setString(6, DBUtil.fixSqlFieldValue(textbook.getT_supporting()));
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public boolean updateTextbook(Textbook textbook) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(TEXTBOOK_UPDATE_SQL);
            psmt.setString(1, DBUtil.fixSqlFieldValue(textbook.getT_name()));
            psmt.setString(2, DBUtil.fixSqlFieldValue(textbook.getT_publisher()));
            psmt.setDate(3, textbook.getT_time());
            psmt.setString(4, DBUtil.fixSqlFieldValue(textbook.getT_contribution()));
            psmt.setString(5, DBUtil.fixSqlFieldValue(textbook.getT_supporting()));
            psmt.setString(6, DBUtil.fixSqlFieldValue(textbook.getA_id()));
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public Textbook getTextbook(String achievement_id) {
        Textbook textbook = null;
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(TEXTBOOK_SELECT_SQL);
            psmt.setString(1, achievement_id);
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                textbook = new Textbook();
                textbook.setA_id(rs.getString("achievement_id"));
                textbook.setT_name(rs.getString("textbook_name"));
                textbook.setT_publisher(rs.getString("textbook_publisher"));
                textbook.setT_time(rs.getDate("textbook_time"));
                textbook.setT_contribution(rs.getString("contribution"));
                textbook.setT_supporting(rs.getString("supporting"));
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return textbook;
    }

    public List<Textbook> getAllTextbooks() {
        List<Textbook> textbooks = new ArrayList<>();
        Connection con = null;
        try {
            con = getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(TEXTBOOK_SELECT_ALL_SQL);
            while (rs.next()) {
                Textbook textbook = new Textbook();
                textbook.setA_id(rs.getString("achievement_id"));
                textbook.setT_name(rs.getString("textbook_name"));
                textbook.setT_publisher(rs.getString("textbook_publisher"));
                textbook.setT_time(rs.getDate("textbook_time"));
                textbook.setT_contribution(rs.getString("contribution"));
                textbook.setT_supporting(rs.getString("supporting"));
                textbooks.add(textbook);
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return textbooks;
    }

    @Override
    public List<Textbook> findTextbooks(SearchCriteriaTextbook searchCriteriaTextbook) {
        List<Textbook> textbooks = new ArrayList<>();
        Connection con = null;
        try {
            // 构建SQL语句
            StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM textbook_table WHERE 1=1");
            if (searchCriteriaTextbook.getA_id() != null) {
                sqlBuilder.append(" AND achievement_id LIKE ?");
            }
            if (searchCriteriaTextbook.getT_name() != null) {
                sqlBuilder.append(" AND textbook_name LIKE ?");
            }
            if (searchCriteriaTextbook.getT_publisher() != null) {
                sqlBuilder.append(" AND textbook_publisher LIKE ?");
            }
            if (searchCriteriaTextbook.getT_time() != null) {
                sqlBuilder.append(" AND textbook_time LIKE ?");
            }
            if (searchCriteriaTextbook.getT_contribution() != null) {
                sqlBuilder.append(" AND contribution LIKE ?");
            }
            if (searchCriteriaTextbook.getT_supporting() != null) {
                sqlBuilder.append(" AND supporting LIKE ?");
            }
            String sql = sqlBuilder.toString();

            // 执行查询
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(sql);
            int index = 1;
            if (searchCriteriaTextbook.getA_id() != null) {
                psmt.setString(index++, searchCriteriaTextbook.getA_id());
            }
            if (searchCriteriaTextbook.getT_name() != null) {
                psmt.setString(index++, searchCriteriaTextbook.getT_name());
            }
            if (searchCriteriaTextbook.getT_publisher() != null) {
                psmt.setString(index++, searchCriteriaTextbook.getT_publisher());
            }
            if (searchCriteriaTextbook.getT_time() != null) {
                psmt.setDate(index++, searchCriteriaTextbook.getT_time());
            }
            if (searchCriteriaTextbook.getT_contribution() != null) {
                psmt.setString(index++, searchCriteriaTextbook.getT_contribution());
            }
            if (searchCriteriaTextbook.getT_supporting() != null) {
                psmt.setString(index++, searchCriteriaTextbook.getT_supporting());
            }
            ResultSet rs = psmt.executeQuery();
            while (rs.next()) {
                String a_id = rs.getString("achievement_id");
                String t_name = rs.getString("textbook_name");
                String t_publisher = rs.getString("textook_publisher");
                Date t_time = rs.getDate("textbook_time");
                String t_contribution = rs.getString("contribution");
                String t_supporting = rs.getString("supporting");
                Textbook textbook = new Textbook(a_id, t_name, t_publisher, t_time, t_contribution, t_supporting);
                textbooks.add(textbook);
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return textbooks;
    }
}